Re: filemaker to pgsql ? - Mailing list pgsql-general

At 3:06 PM -0500 11/6/00, Isaac wrote:
>hello,
>
>has anyone converted files from filemaker to postgres? I'm figuring I'll
>just export everything into tabbed text files and then use the perl
>extensions to parse it out into INSERT queries. If anyone has any experience
>(or code!) to share on this process, it would be great to hear about it.
>
>--i

Here is a very unfinished/unpolished (but working) script to insert
comma delimited files into postgres tables using perl:

#!/usr/bin/perl
#
# Tool to import tab or comma delimited files into a database
#
# Start with comma
#
#print "Filename:";
#chomp ($file = <>);

use DBI;

$file = "participants.txt";

open (FILE, "$file");
@lines = <FILE>;

#assume the first row are column names that conform to db field names
$colnames = shift (@lines);

@cols = split (',', $colnames);
$num_cols = $#cols;

$dbname = "st";
$table = "profile";

my $dbh = DBI->connect("dbi:Pg:dbname=$dbname","","") or die "Can't
connect to database\n";

foreach (@lines) {

     #Put the lines in a hash
     %rows = (); $i=0;
     @data = split (',');
     foreach $col (@cols) {
         $rows{$col} = $data[$i];
         $i++;
     }


     # Build a SQL statement to insert each line from file

     $i=0;
     $sql0 = "INSERT INTO $table (";
     @sqla = (); @sqlb = ();
     foreach $key (keys %rows) {
             $sqla[$i] = $key;
             $sqlb[$i] = $rows{$key};
             $i++;
         }

      $sql1 = join (',',@sqla);
      $sql2 = ") VALUES ('";
      $sql3 = join ('\',\'',@sqlb);
      $sql4 = "')";
      $sql = "$sql0$sql1$sql2$sql3$sql4";

      print "SQL:$sql\n";



      my $sth = $dbh->prepare("$sql") or die "Can't prepare SQL
statement: $DBI::errstr\n";
      $sth->execute or die "Can't execute: $DBI::errstr\n";
      $sth->finish;

}

$dbh->disconnect;
--
--------------------------------
Michelle Murrain Ph.D., President
Norwottuck Technology Resources
mpm@norwottuck.com
http://www.norwottuck.com